# _*_ coding:utf-8 _*_
# @File  : daily_receipt.py
# @Time  : 2021-09-27  10:56
# @Author: zizle
import os
import datetime
import pandas as pd
from fastapi import APIRouter, Depends, Query, UploadFile, Form, Body
from interfaces.depends import admin_logged_require, verify_date
from status import r_status
from configs import APP_DIR
from db import FAConnection
from spiders import futures_daily_receipt

daily_receipt_api = APIRouter()


# --------------------- 上传仓单数据文件 --------------------

@daily_receipt_api.post('/receipt-file/', summary='上传仓单文件')
async def save_daily_quotes_file(person: dict = Depends(admin_logged_require), ex: str = Query(...),
                                 date: datetime.datetime = Depends(verify_date), receipt_file: UploadFile = Form(...)):
    if ex not in ['cffex', 'czce', 'dce', 'shfe']:
        return {'code': r_status.NOT_CONTENT, 'message': '上传失败,param `ex` error!'}
    suffix = os.path.splitext(receipt_file.filename)[1]
    temp_filepath = os.path.join(APP_DIR, f'temp/{ex}/receipt/{date.strftime("%Y%m%d")}{suffix}')
    content = await receipt_file.read()  # 将文件保存到目标位置
    with open(temp_filepath, "wb") as fp:
        fp.write(content)
    await receipt_file.close()
    return {'code': r_status.CREATED_SUCCESS, 'message': '上传仓单文件成功!'}


@daily_receipt_api.get('/daily-receipt/', summary='获取日仓单数据')
async def get_daily_receipt(date: str = Query(...), ex: str = Query(...)):
    try:
        receipt_date = datetime.datetime.strptime(date, '%Y-%m-%d')
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': 'Query param date can not format `%Y-%m-%d`!', 'data': []}
    ex = ex.upper()
    # 获取数据
    if ex == 'CZCE':
        spider = futures_daily_receipt.CZCEDailyReceipt(receipt_date=receipt_date)
    elif ex == 'DCE':
        spider = futures_daily_receipt.DCEDailyReceipt(receipt_date=receipt_date)
    elif ex == 'SHFE':
        spider = futures_daily_receipt.SHFEDailyReceipt(receipt_date=receipt_date)
    else:
        return {'code': r_status.VALIDATE_ERROR, 'message': 'ex validate error!', 'data': []}
    rep_data, msg = spider.get_daily_receipt()
    # 处理数据
    df = pd.DataFrame(rep_data)
    data_str = receipt_date.strftime('%Y-%m-%d')
    df['receipt_date'] = [data_str for _ in range(df.shape[0])]
    df.sort_values(by=['variety_en'], inplace=True)
    rep_data = df.to_dict(orient='records')
    return {'code': r_status.SUCCESS, 'message': f'获取{date} {ex}的仓单数据成功!', 'data': rep_data}


@daily_receipt_api.post('/daily-receipt/', summary='保存仓单数据')
async def save_daily_receipt(receipt_data: list = Body(...)):
    creator = 1
    # 处理数据
    df = pd.DataFrame(receipt_data)
    if df.empty:
        return {'code': r_status.CREATED_SUCCESS, 'message': '没有仓当数据可以保存！', 'count': 0}
    df['receipt_ts'] = df['receipt_date'].apply(lambda x: int(datetime.datetime.strptime(x, '%Y-%m-%d').timestamp()))
    df['creator'] = creator
    # 去除0仓单的数据
    df['receipt'] = df['receipt'].apply(float)
    df['increase'] = df['increase'].apply(float)
    df = df[(df['receipt'] != 0) | (df['increase'] != 0)]
    if df.empty:
        return {'code': r_status.CREATED_SUCCESS, 'message': '没有仓当数据可以保存！', 'count': 0}
    df['warehouse'] = df['warehouse'].apply(lambda x: x.strip())
    save_data = df.to_dict(orient='records')
    # 查询第一个数据是否存在，存在就认为这组数据已经保存了
    db = FAConnection(conn_name='Save Futures Daily Receipt')
    first_data = save_data[0]
    exist_sql = 'SELECT id FROM dat_futures_daily_receipt WHERE receipt_ts=%s AND variety_en=%s LIMIT 1;'
    exist = db.query(exist_sql, param=[first_data['receipt_ts'], first_data['variety_en']], keep_conn=True, fetchone=True)[0]
    if exist:
        db.close()
        return {'code': r_status.CREATED_SUCCESS, 'message': '该仓单数据已经保存过，不用重复保存!', 'count': 0}
    # 保存数据
    save_sql = 'INSERT IGNORE INTO dat_futures_daily_receipt (creator,receipt_ts,variety_en,warehouse,receipt,' \
               'increase,ex_total) ' \
               'VALUES (%(creator)s,%(receipt_ts)s,%(variety_en)s,%(warehouse)s,%(receipt)s,' \
               '%(increase)s,%(ex_total)s);'

    count, _ = db.insert(save_sql, param=save_data, many=True, keep_conn=True)
    db.correct_table_auto_increment(table_name='dat_futures_daily_receipt', finish_closed=True)
    return {'code': r_status.CREATED_SUCCESS, 'message': f'保存仓单日报入库数量{count}！', 'count': count}


# --------------------- 维护 仓单数据 --------------------

@daily_receipt_api.get('/', summary='查询仓单数据')
async def get_rank_data(date: str = Query(...)):
    try:
        receipt_date = datetime.datetime.strptime(date, '%Y-%m-%d')
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': 'Query param date can not format `%Y-%m-%d`!', 'data': []}
    query_sql = 'SELECT id,receipt_ts,variety_en,warehouse,receipt,increase,is_active,ex_total ' \
                'FROM dat_futures_daily_receipt ' \
                'WHERE receipt_ts=%s;'
    db = FAConnection(conn_name='Query-Receipt')
    records = db.query(query_sql, [int(receipt_date.timestamp())])
    df = pd.DataFrame(records)
    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '查询仓单数据为空!', 'data': []}
    df['receipt_date'] = df['receipt_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    rep_data = df.to_dict(orient='records')
    return {'code': r_status.SUCCESS, 'message': '查询仓单数据成功!', 'data': rep_data}

